-- @owner: hejiahuan11
-- @date: 2025-03-31
-- @testpoint: hash分区表结合connect by子句

--step1:创建hash分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_002 cascade;
create table t_connectby_002(e_id int,e_name varchar(10),l_id int)
partition by hash(e_id)
(partition p1,
 partition p2,
 partition p3,
 partition p4,
 partition p5);

insert into t_connectby_002 values(30,'arise',10);
insert into t_connectby_002 values(20,'jack',20);
insert into t_connectby_002 values(50,'judy',20);
insert into t_connectby_002 values(10,'lion',30);
insert into t_connectby_002 values(40,'scott',20);
insert into t_connectby_002 values(30,'tiger',30);
insert into t_connectby_002 values(10,'king',10);


--step2:查询以l_id为10开始的节点的直属节点  expect:成功
select * from t_connectby_002 start with e_id = 10 connect by nocycle prior l_id = e_id;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_002 start with e_id = 10 connect by nocycle prior l_id = e_id;

--step4:清理环境   expect:成功
drop table t_connectby_002 cascade;